package com.pcbly.web.persistence.menu;

import java.util.List;

import org.hibernate.SQLQuery;

import com.pcbly.web.bean.system.MenuBean;
import com.pcbly.web.bean.system.MenuChildBean;
import com.pcbly.web.bean.user.UserBean;
import com.pcbly.web.components.utils.random.GUID;
import com.pcbly.web.persistence.HibernateBaseDao;

@SuppressWarnings("unchecked")
public class MenuHibernateDao extends HibernateBaseDao<MenuBean> implements
		MenuDao {

	/**
	 * 
	 */
	private static final long serialVersionUID = 7672817704215028239L;

	public MenuHibernateDao() {
		super();
	}

	protected Class<MenuBean> getEntityClass() {
		return MenuBean.class;
	}

	public List<MenuBean> queryListsByRoleIdAndParentId(String roleIds,
			String parentId) throws Exception {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("select menu.ID,menu.PARENTID  ");
		sb.append(",menu.NAME_,menu.DESCN_,menu.IMAGE_  ");
		sb.append(",menu.FORWARD_,menu.TARGET_,menu.ORDERBY_  ");
		sb.append(" from SYS_MENU menu,SYS_ROLE role ");
		sb.append(",sys_menu_role mr ");
		sb.append(" where menu.ID=mr.MENUID and  ");
		sb.append("  role.ID=mr.ROLEID and  ");
		if (parentId == null || "".equals(parentId)) {
			sb.append(" menu.PARENTID is null and  ");
		} else {
			sb.append(" menu.PARENTID='");
			sb.append(parentId);
			sb.append("' and ");
		}
		sb.append(" role.ID='");
		sb.append(roleIds);
		sb.append("' order by  menu.ORDERBY_  ");
		if (logger.isDebugEnabled())
			logger.debug(sb);
		SQLQuery sqlq = getSession().createSQLQuery(sb.toString());
		List<MenuBean> find = sqlq.addEntity(MenuBean.class).list();
		return find;
	}

	public List<MenuBean> queryListsByUserIdAndParentId(String userId,
			String parentId) throws Exception {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("select menu.ID,menu.PARENTID  ");
		sb.append(",menu.NAME_,menu.DESCN_,menu.IMAGE_  ");
		sb.append(",menu.FORWARD_,menu.TARGET_,menu.ORDERBY_  ");
		sb.append(" from SYS_MENU menu,SYS_ROLE role_,SYS_USER user_ ");
		sb.append(",SYS_USER_ROLE ur,sys_menu_role mr ");
		sb.append(" where menu.ID=mr.MENUID and  ");
		sb.append("  role_.ID=mr.ROLEID and  ");
		sb.append(" user_.ID=ur.USERID and  ");
		sb.append(" role_.ID=ur.ROLEID and  ");
		if (parentId == null || "".equals(parentId)) {
			sb.append(" menu.PARENTID is null and  ");
		} else {
			sb.append(" menu.PARENTID='");
			sb.append(parentId);
			sb.append("' and ");
		}
		sb.append(" user_.ID='");
		sb.append(userId);
		sb.append("' order by  menu.ORDERBY_  ");
		if (logger.isDebugEnabled())
			logger.debug(sb);
		SQLQuery sqlq = getSession().createSQLQuery(sb.toString());
		List<MenuBean> find = sqlq.addEntity(MenuBean.class).list();
		return find;
	}

	public List<MenuBean> queryListsByUserAndParentId(UserBean user,
			String parentId) throws Exception {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("select menu.ID,menu.PARENTID  ");
		sb.append(",menu.NAME_,menu.DESCN_,menu.IMAGE_  ");
		sb.append(",menu.FORWARD_,menu.TARGET_,menu.ORDERBY_  ");
		sb.append(" from SYS_MENU menu,SYS_ROLE role_,SYS_USER user_ ");
		sb.append(",SYS_USER_ROLE ur,sys_menu_role mr ");
		sb.append(" where menu.ID=mr.MENUID and  ");
		sb.append("  role_.ID=mr.ROLEID and  ");
		sb.append(" user_.ID=ur.USERID and  ");
		sb.append(" role_.ID=ur.ROLEID and  ");
		if (parentId == null || "".equals(parentId)) {
			sb.append(" menu.PARENTID is null and  ");
		} else {
			sb.append(" menu.PARENTID='");
			sb.append(parentId);
			sb.append("' and ");
		}
		sb.append(" user_.ID='");
		sb.append(user.getId());
		sb.append("' order by  menu.ORDERBY_  ");
		if (logger.isDebugEnabled())
			logger.debug(sb);
		SQLQuery sqlq = getSession().createSQLQuery(sb.toString());
		List<MenuBean> find = sqlq.addEntity(MenuBean.class).list();
		return find;
	}

	public List<MenuBean> queryListsByParentId(String parentId)
			throws Exception {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("from MenuBean where parent.id=?  order by orderBy "); 
		if (parentId == null || "".equals(parentId)) {
			parentId="0"; 
		} 
		 if (logger.isDebugEnabled())
		 logger.debug(sb);
		List<MenuBean> find = getSession().createQuery(sb.toString()).setString(0, parentId).list(); 
		return find;
	}

	public List<MenuBean> loadTopMenus() throws Exception {
		String hql = "select t.* from sys_menu t where t.PARENTID='0' order by t.ID ";

		return getSession().createSQLQuery(hql).addEntity(MenuBean.class)
				.list();
	}

	@Override
	public MenuBean getMenuById(String id) throws Exception {
		// TODO Auto-generated method stub
		return (MenuBean)getSession().createQuery("from MenuBean where id=?").setString(0, id).uniqueResult();
	}

	@Override
	public List<MenuBean> getMenuList() throws Exception {
		// TODO Auto-generated method stub
		return super.getAll();
	}

	@Override
	public List<MenuBean> loadMenusByUserId(String userId) throws Exception {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("select menu.ID,menu.PARENTID  ");
		sb.append(",menu.NAME_,menu.DESCN_,menu.IMAGE_  ");
		sb.append(",menu.FORWARD_,menu.TARGET_,menu.ORDERBY_  ");
		sb.append(" from SYS_MENU menu,SYS_ROLE role_,SYS_USER user_ ");
		sb.append(",SYS_USER_ROLE ur,sys_menu_role mr ");
		sb.append(" where menu.ID=mr.MENUID and  ");
		sb.append("  role_.ID=mr.ROLEID and  ");
		sb.append(" user_.ID=ur.USERID and  ");
		sb.append(" role_.ID=ur.ROLEID and  ");
		sb.append(" user_.ID='");
		sb.append(userId);
		sb.append("' order by  menu.ORDERBY_  ");
		if (logger.isDebugEnabled())
			logger.debug(sb);
		SQLQuery sqlq = getSession().createSQLQuery(sb.toString());
		List<MenuBean> find = sqlq.addEntity(MenuBean.class).list();
		return find;
	}

	@Override
	public void removeMenu(String id) throws Exception {
		// TODO Auto-generated method stub
		MenuBean bean = super.get(MenuBean.class, id);
		if (bean == null) {
			throw new Exception("no find data!");
		} 
		super.remove(bean);
		String hsql = "delete from sys_menu where PARENTID ='" + id + "'";
		getSession().createSQLQuery(hsql).executeUpdate();
	}

	@Override
	public void saveMenu(MenuBean menu) throws Exception {
		// TODO Auto-generated method stub
		if(logger.isDebugEnabled()){
			logger.debug(menu.getName());
		}
		if (menu.getId() == null || "".equals(menu.getId().trim())){
			String id=(new GUID()).toString();
			menu.setId(id);
			getHibernateTemplate().save(menu);
		}
		else{
			getHibernateTemplate().update(menu);
		}
	}

	@Override
	public void removeMenuByIds(String ids) throws Exception {
		// TODO Auto-generated method stub
		ids = ids.replaceAll(",", "','");
		
		String hsql = "delete from sys_menu where PARENTID in( '" + ids + "')";
		getSession().createSQLQuery(hsql).executeUpdate();
		hsql = "delete from sys_menu where ID in( '" + ids + "')";
		getSession().createSQLQuery(hsql).executeUpdate();
	}

	@Override
	public List<MenuChildBean> queryMenuChildBeansByParentId(String UserId,
			String parentId) throws Exception {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT a.id,a.name_,a.DESCN_,a.image_,");
		sb.append("a.forward_,a.target_,a.orderby_,COUNT(b.id) child_ ");
		sb.append(" FROM sys_menu a LEFT JOIN sys_menu b ON a.id=b.PARENTID ");
		sb.append(" LEFT JOIN sys_menu_role c ON a.id=c.menuId ");
		sb.append(" LEFT JOIN sys_user_role d ON c.roleId=d.roleID ");
		sb.append(" WHERE a.parentId=? ");
		sb.append(" and d.USERID=? ");
		sb.append(" GROUP BY a.id,a.name_,a.DESCN_,a.image_,");
		sb.append("a.forward_,a.target_,a.orderby_ ");
		sb.append(" order by a.orderby_ ");
		return getSession().createSQLQuery(sb.toString()).addEntity(
				MenuChildBean.class).setString(0, parentId)
				.setString(1, UserId).list();
	}

	@Override
	public List<MenuChildBean> queryMenuChildBeansByParentId(String parentId)
			throws Exception {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT a.id,a.name_,a.DESCN_,a.image_,");
		sb.append("a.forward_,a.target_,a.orderby_,COUNT(b.id) child_ ");
		sb.append(" FROM sys_menu a LEFT JOIN sys_menu b ON a.id=b.PARENTID ");
		sb.append(" WHERE a.parentId=? ");
		sb.append(" GROUP BY a.id,a.name_,a.DESCN_,a.image_,");
		sb.append("a.forward_,a.target_,a.orderby_ ");
		sb.append(" order by a.orderby_ ");
		if(logger.isDebugEnabled()){
			logger.debug(sb);
		}
//		System.out.println(sb);
		if (parentId == null || "".equals(parentId)) {
			parentId="0";
		}
		return getSession().createSQLQuery(sb.toString()).addEntity(
				MenuChildBean.class).setString(0, parentId).list();
	}

}
